<?php
class ModelCatalogProduct extends Model {
	public function getProduct($product_id) {
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, pd.description AS description, p.image, p.size_guide, m.name AS manufacturer, m.description AS manufacturer_desc, ss.name AS stock FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
	
		return $query->row;
	}

	public function getProducts() {
		$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, wcd.unit AS weight_class FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "weight_class_description wcd ON (p.weight_class_id = wcd.weight_class_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
	
		return $query->rows;
	}
	
	public function getProductsByCategoryId($category_id, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
		$sql = "SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, " . 
            "   (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r " .
            "   WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating " .
            " FROM " . DB_PREFIX . "product p " . 
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) " .
            " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) " . 
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' " . 
            " AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' " .
            " AND p2c.category_id = '" . (int)$category_id . "'";
		
		$sort_data = array(
			'pd.name',
			'p.sort_order',
			'special',
			'rating',
			'p.price',
			'p.model'
		);
			
		if (in_array($sort, $sort_data)) {
			if ($sort == 'pd.name' || $sort == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $sort . ")";
			} else {
				$sql .= " ORDER BY " . $sort;
			}
		} else {
			$sql .= " ORDER BY p.sort_order";	
		}
			
		if ($order == 'DESC') {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		
        if ($start < 0) {
            $start = 0;
        }
            
        $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
		
		$query = $this->db->query($sql);
								  
		return $query->rows;
	} 
	
    public function getProductsByCategoryIdSortPrice($category_id, $sort = 'pcs.product_sort_order', $order = 'ASC', $limit = 20, $page = 0) {
        $sql = "SELECT DISTINCT p.*, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, pcs.product_sort_order " . 
            //",   (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r " .
            //"   WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating " .
            " FROM " . DB_PREFIX . "product p " .
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) " . 
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " . 
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) " . 
            " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) " . 
            " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_category_sort pcs ON (pcs.category_id = p2c.category_id AND pcs.product_id = p.product_id) " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' " . 
            " AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '" . (int)$category_id . "'";
        
        //$sql .= " ORDER BY p.price";      
        //$sql .= $order == 'DESC' ? " DESC" : " ASC";
        
        $sort_data = array(
            'pd.name',
            //'p.sort_order',             
            'pcs.product_sort_order',
            'p.price',  
            'p.date_added',
            'p.model',
            'm.name',
        );
                
        if (in_array($sort, $sort_data)) {
            if ($sort == 'pd.name' || $sort == 'p.model' || $sort == 'm.name') {
                $sql .= " ORDER BY LCASE(" . $sort . ")";
            } 
            else {
                $sql .= " ORDER BY " . $sort;
            }
        } 
        else {
            $sql .= " ORDER BY p.sort_order";    
        }
            
        if ($order == 'DESC') {
            $sql .= " DESC";
        } 
        else {
            $sql .= " ASC";
        }              
    
        if((int)$limit > 0){
             $start = ($page - 1) * $limit;
             $start = ($start < 0) ? 0 : $start;
            
            $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
        }
        
        $query = $this->db->query($sql);
                                  
        return $query->rows;
    } 
    
    public function getProductsByCategoryAttributeSortPrice($category_id, $attribute_ids, $value_ids, $sort = 'pcs.product_sort_order', $order = 'ASC', $limit = 20, $page = 0) {
        $sql = "SELECT DISTINCT p.*, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, pcs.product_sort_order " . 
            //",    (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r " . 
            //"     WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating " . 
            " FROM " . DB_PREFIX . "product p " .
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) " .
            " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_option po ON (p.product_id = po.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (po.product_option_id = pov.product_option_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_category_sort pcs ON (pcs.product_id = p.product_id AND pcs.category_id = p2c.category_id) " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' " .
            " AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '" . (int)$category_id . "'" . 
            //" AND po.attribute_id IN (" . implode(',', $attribute_ids) . ") AND pov.attribute_value_id IN (" . implode(',', $value_ids) . ")";
			//" AND pov.attribute_value_id IN (" . implode(',', $value_ids) . ")";
			" AND pov.attribute_value_id = " . current($value_ids);
        
        //$sql .= " ORDER BY p.price";      
        //$sql .= $order == 'DESC' ? " DESC" : " ASC";
        $sort_data = array(
            'pd.name',
            //'p.sort_order',
            'pcs.product_sort_order',
            'p.price',  
            'p.date_added',
            'p.model',
            'm.name',
        );
                
        if (in_array($sort, $sort_data)) {
            if ($sort == 'pd.name' || $sort == 'p.model' || $sort == 'm.name') {
                $sql .= " ORDER BY LCASE(" . $sort . ")";
            }             
            else {
                $sql .= " ORDER BY " . $sort;
            }
        } 
        else {
            $sql .= " ORDER BY p.sort_order";    
        }
            
        if ($order == 'DESC') {
            $sql .= " DESC";
        } 
        else {
            $sql .= " ASC";
        } 
    
        if((int)$limit > 0){
             $start = ($page - 1) * $limit;
             $start = ($start < 0) ? 0 : $start;
            
            $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
        }
         
        $query = $this->db->query($sql);
                                  
        return $query->rows;
    } 
    
	public function getTotalProductsByCategoryId($category_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_category p2c LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND p2c.category_id = '" . (int)$category_id . "'");

		return $query->row['total'];
	}

    public function getTotalProductsByCategoryAttribute($category_id = 0, $attribute_ids, $value_ids) {
        $sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product_to_category p2c " . 
            " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_option po ON (p.product_id = po.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (po.product_option_id = pov.product_option_id) " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND p2c.category_id = '" . (int)$category_id . "' " . 
            //" AND po.attribute_id IN (" . implode(',', $attribute_ids) . ") AND pov.attribute_value_id IN (" . implode(',', $value_ids) . ")";
			" AND pov.attribute_value_id IN (" . implode(',', $value_ids) . ")";
           
        $query = $this->db->query($sql);

        return $query->row['total'];
    }
    
	public function getProductsByManufacturerId($manufacturer_id, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
		$sql = "SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, " .
            "   (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r " .
            "   WHERE p.product_id = r.product_id " .
            "   GROUP BY r.product_id " .
            " ) AS rating " . 
            " FROM " . DB_PREFIX . "product p " . 
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " .
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) " . 
            " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND m.manufacturer_id = '" . (int)$manufacturer_id. "'";
        
		$sort_data = array(
			'pd.name',
			'p.sort_order',
			'special',
			'rating',
			'p.price',
			'p.model'
		);
			
		if (in_array($sort, $sort_data)) {
			if ($sort == 'pd.name' || $sort == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $sort . ")";
			} else {
				$sql .= " ORDER BY " . $sort;
			}
		} else {
			$sql .= " ORDER BY p.sort_order";	
		}
			
		if ($order == 'DESC') {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if ($start < 0) {
			$start = 0;
		}
		
		$sql .= " LIMIT " . (int)$start . "," . (int)$limit;
		
		$query = $this->db->query($sql);
		
		return $query->rows;
	} 

	public function getTotalProductsByManufacturerId($manufacturer_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE status = '1' AND date_available <= NOW() AND manufacturer_id = '" . (int)$manufacturer_id . "'");
		
		return $query->row['total'];
	}
	
	public function getProductsByTag($tag, $category_id = 0, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
		if ($tag) {
		
			$sql = "SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_tags pt ON (p.product_id = pt.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'  AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND (LCASE(pt.tag) = '" . $this->db->escape(strtolower($tag)) . "'";

			$keywords = explode(" ", $tag);
						
			foreach ($keywords as $keyword) {
				$sql .= " OR LCASE(pt.tag) = '" . $this->db->escape(strtolower($keyword)) . "'";
			}
			
			$sql .= ")";
			
			if ($category_id) {
				$data = array();
				
				$this->load->model('catalog/category');
				
				$string = rtrim($this->getPath($category_id), ',');
				
				foreach (explode(',', $string) as $category_id) {
					$data[] = "category_id = '" . (int)$category_id . "'";
				}
				
				$sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
			}
		
			$sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
		
			$sort_data = array(
				'pd.name',
				'p.sort_order',
				'special',
				'rating',
				'p.price',
				'p.model'
			);
				
			if (in_array($sort, $sort_data)) {
				if ($sort == 'pd.name' || $sort == 'p.model') {
					$sql .= " ORDER BY LCASE(" . $sort . ")";
				} else {
					$sql .= " ORDER BY " . $sort;
				}
			} else {
				$sql .= " ORDER BY p.sort_order";	
			}
			
			if ($order == 'DESC') {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			if ($start < 0) {
				$start = 0;
			}
		
			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;
			
			$query = $this->db->query($sql);
			
			$products = array();
			
			foreach ($query->rows as $key => $value) {
				$products[$value['product_id']] = $this->getProduct($value['product_id']);
			}
			
			return $products;
		}
	}
	
	public function getProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
		if ($keyword) {
						
			$sql = "SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";
			
			if (!$description) {
				$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
			} else {
				$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
			}
			
			if (!$model) {
				$sql .= ")";
			} else {
				$sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
			}
			
			if ($category_id) {
				$data = array();
				
				$this->load->model('catalog/category');
				
				$string = rtrim($this->getPath($category_id), ',');
				
				foreach (explode(',', $string) as $category_id) {
					$data[] = "category_id = '" . (int)$category_id . "'";
				}
				
				$sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
			}
		
			$sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
		
			$sort_data = array(
				'pd.name',
				'p.sort_order',
				'special',
				'rating',
				'p.price',
				'p.model'
			);
				
			if (in_array($sort, $sort_data)) {
				if ($sort == 'pd.name' || $sort == 'p.model') {
					$sql .= " ORDER BY LCASE(" . $sort . ")";
				} else {
					$sql .= " ORDER BY " . $sort;
				}
			} else {
				$sql .= " ORDER BY p.sort_order";	
			}
			
			if ($order == 'DESC') {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			if ($start < 0) {
				$start = 0;
			}
		
			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;

			$query = $this->db->query($sql);
						
			return $query->rows;
		}
		return 0;	
	}   
	     	
	public function getTotalProductsByTag($tag, $category_id = 0) {
		if ($tag) {
		
			$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_tags pt ON (p.product_id = pt.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'  AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND (LCASE(pt.tag) = '" . $this->db->escape(strtolower($tag)) . "'";

			$keywords = explode(" ", $tag);
						
			foreach ($keywords as $keyword) {
				$sql .= " OR LCASE(pt.tag) = '" . $this->db->escape(strtolower($keyword)) . "'";
			}
			
			$sql .= ")";
			
			if ($category_id) {
				$data = array();
				
				$this->load->model('catalog/category');
				
				$string = rtrim($this->getPath($category_id), ',');
				
				foreach (explode(',', $string) as $category_id) {
					$data[] = "category_id = '" . (int)$category_id . "'";
				}
				
				$sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
			}
		
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
					
			$query = $this->db->query($sql);
			
			if ($query->num_rows) {
				return $query->row['total'];
			} else {
				return 0;
			}
		}
		return 0;
	}
	
    private function getCategoriesList($category_id){
        $sql = "SELECT category_id FROM category WHERE status = '1' AND parent_id = '" . (int)$category_id . "'";
        $query = $this->db->query($sql); 
        
        $rows = array($category_id);
        $level1 = array();
        $categories = $query->rows;
        foreach($categories as $row){
            $rows[] = $row['category_id'];
            $level1[] = $row['category_id']; 
        }
       
        if(!empty($level1)){            
            $sql = "SELECT category_id FROM category WHERE status = '1' AND parent_id IN (" . implode(',', $level1) . ")";
            
            $query = $this->db->query($sql); 
            $level2 = $query->rows; 
            foreach($level2 as $row_item){
                $rows[] = $row_item['category_id'];                
            }            
        } 
                                                   
        $list = implode(',', $rows);            
        
        return $list;
    }
    
    public function getProductsByConditional($category_id, $manufacturer_id, $sort = 'pms.product_msort_order', $order = 'ASC', $start = 0, $limit = 20) {        
        $categories_list = $this->getCategoriesList($category_id);
                    
        $sql = "SELECT DISTINCT p.*, pd.name AS name, m.name AS manufacturer, ss.name AS stock, pms.product_msort_order " .            
            " FROM " . DB_PREFIX . "product p " .
            " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id)" .
            " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id)" .
            " LEFT JOIN " . DB_PREFIX . "product_to_category pc ON (p.product_id = pc.product_id)" .
            " LEFT JOIN " . DB_PREFIX . "category_description cd ON (pc.category_id = cd.category_id)" .
            //" LEFT JOIN " . DB_PREFIX . "product_category_sort pcs ON (pcs.product_id = p.product_id AND pcs.category_id = pc.category_id AND pcs.category_id IN (" . $categories_list . ")) " .
            " LEFT JOIN " . DB_PREFIX . "product_manufacturer_sort pms ON (pms.product_id = p.product_id AND pms.manufacturer_id = p.manufacturer_id AND IFNULL(pms.category_id, 0) = '" . (int)$category_id . "') " .
            " WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'" . 
            " AND m.manufacturer_id = '" . (int)$manufacturer_id. "' AND pc.category_id IN (" . $categories_list . ") AND cd.language_id = '" . (int)$this->config->get('config_language_id'). "'";

        $sort_data = array(
            'pd.name',
            //'p.sort_order',            
            'pms.product_msort_order',
            'p.price',
            'p.date_added',
            'p.model',
            'cd.name',
        );
            
        if (in_array($sort, $sort_data)) {
            if ($sort == 'pd.name' || $sort == 'p.model' || $sort == 'cd.name') {
                $sql .= " ORDER BY LCASE(" . $sort . ")";
            } else {
                $sql .= " ORDER BY " . $sort;
            }
        } else {
            $sql .= " ORDER BY p.sort_order";    
        }
            
        if ($order == 'DESC') {
            $sql .= " DESC";
        } else {
            $sql .= " ASC";
        }

        if ($start < 0) {
            $start = 0;
        }
        
        $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
       //echo $sql; 
        $query = $this->db->query($sql);
        
        return $query->rows;
    } 

    public function getTotalProductsByConditional($category_id, $manufacturer_id = 0) {
        $categories_list = $this->getCategoriesList($category_id);
       
        $sql = "SELECT COUNT(pp.product_id) AS total FROM (" . 
            "   SELECT DISTINCT p.product_id FROM " . DB_PREFIX . "product p " .
            "   LEFT JOIN product_to_category pc ON (p.product_id = pc.product_id)" . 
            "   WHERE p.status = '1' AND p.date_available <= NOW() AND p.manufacturer_id = '" . (int)$manufacturer_id . "' AND pc.category_id IN (" . $categories_list . ")" .
            ") pp";
        
        $query = $this->db->query($sql);
        
        return $query->row['total'];
    }
    
	public function getPath($category_id) {
		$string = $category_id . ',';
		
		$results = $this->model_catalog_category->getCategories($category_id);

		foreach ($results as $result) {
			$string .= $this->getPath($result['category_id']);
		}
		
		return $string;
	}	
	
	public function getLatestProducts($limit) {
		$product_data = $this->cache->get('product.latest.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit);

		if (!$product_data) { 
			$query = $this->db->query("SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY p.date_added DESC LIMIT " . (int)$limit);
		 	 
			$product_data = $query->rows;

			$this->cache->set('product.latest.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit, $product_data);
		}
		
		return $product_data;
	}
	
	public function getPopularProducts($limit) {
		$query = $this->db->query("SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY p.viewed, p.date_added DESC LIMIT " . (int)$limit);
		 	 		
		return $query->rows;
	}
	
	public function getFeaturedProducts($limit) {
		$product_data = $this->cache->get('product.featured.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit);

		if (!$product_data) { 
			$query = $this->db->query("SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product_featured f LEFT JOIN " . DB_PREFIX . "product p ON (f.product_id=p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (f.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' LIMIT " . (int)$limit);
	
			$product_data = $query->rows;

			$this->cache->set('product.featured.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit, $product_data);
		}
		
		return $product_data;
	}

    public function getSpecialFeaturedProducts($limit = 0) {
        $sql = "SELECT * FROM ". DB_PREFIX . "product_special_featured WHERE feature = 'product_home'";
        if($limit){
            $sql .= ' LIMIT 0,'.$limit;
        }

        $query = $this->db->query($sql);
        $product_data = $query->rows;        
        
        foreach($product_data as &$product){
            $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product['product_id'] . "'");
            if ($query->num_rows) {
				$product['tax_class_id'] = $query->row['tax_class_id'];
			}
			else{
				$product['tax_class_id'] = 0;
			}
        }    
        
        return $product_data;
    }

    public function getSpecialLeftProducts($limit = 0) {
        $sql = "SELECT * FROM ". DB_PREFIX . "product_special_featured WHERE feature = 'product_left'";
        if($limit){
            $sql .= ' LIMIT 0,'.$limit;
        }

        $query = $this->db->query($sql);
        $product_data = $query->rows;

        foreach($product_data as &$product){
            $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product['product_id'] . "'");
            if ($query->num_rows) {
                $product['tax_class_id'] = $query->row['tax_class_id'];
            }
            else{
                $product['tax_class_id'] = 0;
            }
        }

        return $product_data;
    }
    
	public function getBestSellerProducts($limit) {
		$product_data = $this->cache->get('product.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit);

		if (!$product_data) { 
			$product_data = array();
			
			$query = $this->db->query("SELECT op.product_id, SUM(op.quantity) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);
			
			foreach ($query->rows as $result) {
				$product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.product_id = '" . (int)$result['product_id'] . "' AND p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
				
				if ($product_query->num_rows) {
					$product_data[] = $product_query->row;
				}
			}

			$this->cache->set('product.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit, $product_data);
		}
		
		return $product_data;
	}
		
	public function updateViewed($product_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = viewed + 1 WHERE product_id = '" . (int)$product_id . "'");
	}
	
	public function getProductOptions($product_id) {
		$product_option_data = array();
		
		$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order");
		
		foreach ($product_option_query->rows as $product_option) {
			$product_option_value_data = array();
			
			$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "' ORDER BY sort_order");
			
			foreach ($product_option_value_query->rows as $product_option_value) {
				$product_option_value_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value_description WHERE product_option_value_id = '" . (int)$product_option_value['product_option_value_id'] . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
			
				if(!empty($product_option_value_description_query->row)){
					$product_option_value_data[] = array(
						'product_option_value_id' => $product_option_value['product_option_value_id'],
						'name'                    => $product_option_value_description_query->row['name'],
						'price'                   => $product_option_value['price'],
						'prefix'                  => $product_option_value['prefix'],
						'product_option_number'   => $product_option_value['product_option_number'],
						'quantity'                => $product_option_value['quantity'],
					);
				}
			}
						
			$product_option_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_description WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
						
        	$product_option_data[] = array(
        		'product_option_id' => $product_option['product_option_id'],
				'name'              => $product_option_description_query->row['name'],
				'option_value'      => $product_option_value_data,
				'sort_order'        => $product_option['sort_order']
        	);
      	}	
		
		return $product_option_data;
	}
	
	public function getActiveProductOptions($product_id) {
		$product_option_data = array();
		
		$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order");
		
		foreach ($product_option_query->rows as $product_option) {
			$product_option_value_data = array();
			
			$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "' ORDER BY sort_order");
			
			foreach ($product_option_value_query->rows as $product_option_value) {
				$product_option_value_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value_description WHERE product_option_value_id = '" . (int)$product_option_value['product_option_value_id'] . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
			
				if(!empty($product_option_value_description_query->row)){
					if($product_option_value['quantity'] > 0){
						$product_option_value_data[] = array(
							'product_option_value_id' => $product_option_value['product_option_value_id'],
							'name'                    => $product_option_value_description_query->row['name'],
							'price'                   => $product_option_value['price'],
							'prefix'                  => $product_option_value['prefix'],
							'product_option_number'   => $product_option_value['product_option_number'],
							'quantity'                => $product_option_value['quantity'],
						);
					}
				}
			}
						
			$product_option_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_description WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
						
        	$product_option_data[] = array(
        		'product_option_id' => $product_option['product_option_id'],
				'name'              => $product_option_description_query->row['name'],
				'option_value'      => $product_option_value_data,
				'sort_order'        => $product_option['sort_order']
        	);
      	}	
		
		return $product_option_data;
	}
	
	public function getProductImages($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");

		return $query->rows;
	}
	
	public function getProductColors($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_color WHERE product_id = '" . (int)$product_id . "'  ORDER BY sort_order, product_color_id");
		
		return $query->rows;
	}
	
	public function getProductColorImages($product_id, $color_id) {		
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_color_image WHERE product_id = '" . (int)$product_id . "' AND color_id = '" . (int)$color_id . "' ORDER BY color_image_id");
		
		return $query->rows;
	}
	
	public function getProductColorById($product_id, $color_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_color WHERE product_id = '" . (int)$product_id . "' AND product_color_id = '" . (int)$color_id . "'");
		
		return isset($query->rows[0]) ? $query->rows[0] : null;
	}
	
	public function getProductTags($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_tags WHERE product_id = '" . (int)$product_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

		return $query->rows;
	}
	
	public function getProductDiscount($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
        $sql = "SELECT price FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity = '1' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY priority ASC, price ASC LIMIT 1";
		$query = $this->db->query($sql);
		
		if ($query->num_rows) {
			return $query->row['price'];
		} else {
			return FALSE;
		}
	}

	public function getProductDiscounts($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		//$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity > 1 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity > 0 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY quantity ASC, priority ASC, price ASC");

		return $query->rows;		
	}
	
	public function getProductSpecial($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
		
		$query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY priority ASC, price ASC LIMIT 1");
		
		if ($query->num_rows) {
			return $query->row['price'];
		} else {
			return FALSE;
		}
	}
	
    public function getProductSpecialPrice($product_id) {
        if ($this->customer->isLogged()) {
            $customer_group_id = $this->customer->getCustomerGroupId();
        } else {
            $customer_group_id = $this->config->get('config_customer_group_id');
        }    
        
        //$query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY priority ASC, price ASC LIMIT 1");
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND quantity > 0 AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY priority ASC, price ASC");
        
        return $query->rows;
    }
    
	public function getProductSpecials($sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 20) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}		

		$sql = "SELECT *, pd.name AS name, p.price, (SELECT ps2.price FROM " . DB_PREFIX . "product_special ps2 WHERE p.product_id = ps2.product_id AND ps2.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps2.date_start = '0000-00-00' OR ps2.date_start < NOW()) AND (ps2.date_end = '0000-00-00' OR ps2.date_end > NOW())) ORDER BY ps2.priority ASC, ps2.price ASC LIMIT 1) AS special, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_special ps ON (p.product_id = ps.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND ps.customer_group_id = '" . (int)$customer_group_id . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW())AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) AND ps.product_id NOT IN (SELECT pd2.product_id FROM " . DB_PREFIX . "product_discount pd2 WHERE p.product_id = pd2.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()))) GROUP BY p.product_id";

		$sort_data = array(
			'pd.name',
			'p.sort_order',
			'special',
			'rating',
			'p.price',
			'p.model'
		);
			
		if (in_array($sort, $sort_data)) {
			if ($sort == 'pd.name' || $sort == 'p.model') {
				$sql .= " ORDER BY LCASE(" . $sort . ")";
			} else {
				$sql .= " ORDER BY " . $sort;
			}
		} else {
			$sql .= " ORDER BY p.sort_order";	
		}
			
		if ($order == 'DESC') {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		
		if ($start < 0) {
			$start = 0;
		}
		
		$sql .= " LIMIT " . (int)$start . "," . (int)$limit;

		$query = $this->db->query($sql);
		
		return $query->rows;
	}	

	public function getTotalProductSpecials() {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}		
		
		$query = $this->db->query("SELECT COUNT(DISTINCT ps.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_special ps ON (p.product_id = ps.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) AND ps.product_id NOT IN (SELECT pd2.product_id FROM " . DB_PREFIX . "product_discount pd2 WHERE p.product_id = pd2.product_id AND pd2.customer_group_id = '" . (int)$customer_group_id . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())))");
		
		if (isset($query->row['total'])) {
			return $query->row['total'];
		} else {
			return 0;	
		}
	}	
	
	public function getProductRelated($product_id) {
		$product_data = array();

		$product_related_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "'");
		
		foreach ($product_related_query->rows as $result) { 
			$product_query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.product_id = '" . (int)$result['related_id'] . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
			
			if ($product_query->num_rows) {
				$product_data[$result['related_id']] = $product_query->row;
			}
		}
		
		return $product_data;
	}
	
	public function getCategories($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
		
		return $query->rows;
	}
	
	public function getCategoriesPath($product_id) { 
		$categories = $this->model_catalog_product->getCategories($product_id);
		reset($categories);
		$cat = current($categories);
		$current_cat = $cat['category_id'];  
		$categories = array();    		  
		 				
		while($current_cat > 0){
			$categories[] = $current_cat;			
			$current_cat = $this->model_catalog_category->getParentCategory($current_cat);				
		}
		
		$categories = array_reverse($categories);
		 
		return implode('_', $categories);
	}
	
	public function getSizeOption($options) {		       
		foreach ($options as $option) { 			
			if(strcasecmp(trim($option['name']), 'størrelse') == 0){              
			 	return $option;
			}         
		}
		
        reset($options);
		return current($options);
	}
	
	public function getColorOption($product_id, $options) {		
		$color = array();			
		foreach ($options as $option) { 			
			if(strcasecmp(trim($option['name']), 'farve') == 0){  
			 	$color = $option;
			 	break;
			}
		}
		
		if(!empty($color)){
			$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_color WHERE product_id = '" . (int)$product_id . "' AND option_value_id = '" . (int)$color['product_option_value_id'] . "' ORDER BY product_color_id");		
			
			$data = $query->rows;
			reset($data);
			$result = current($data);
			$result['name'] = $color['name'];
			 
			return $result;
		}
		
		return array();
	}
	
	public function searchProducts($keyword, $sort = 'pd.name', $order = 'ASC', $start = 0, $limit = 12) {
        if ($keyword) {                        
            //$sql = "SELECT p.*, pd.name AS name, p.image, ss.name AS stock, pcs.product_sort_order FROM " . DB_PREFIX . "product p" .
            $sql = "SELECT p.*, pd.name AS name, p.image, ss.name AS stock FROM " . DB_PREFIX . "product p" .
                " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "stock_status ss  ON (p.stock_status_id = ss.stock_status_id)" . 
                " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_to_category pc ON (p.product_id = pc.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "category_description cd ON (pc.category_id = cd.category_id)" .
                //" LEFT JOIN " . DB_PREFIX . "product_category_sort pcs ON (pcs.product_id = p.product_id AND pcs.category_id = pc.category_id) " .
               // " LEFT JOIN " . DB_PREFIX . "url_alias ua ON (CONCAT('product_id=', p.product_id) = ua.query) " .                  
                " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";        
                        
            $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                " OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                //" OR LCASE(pd.meta_keywords) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                //" OR LCASE(ua.keyword) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" . 
                ")";              
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() AND cd.language_id = '" . (int)$this->config->get('config_language_id'). "' GROUP BY p.product_id";
             
            $sort_data = array(
                'pd.name',
                //'p.sort_order',
                'pcs.product_sort_order',
                'special',
                'rating',
                'p.price',
                'p.model',
                'p.date_added',
                'm.name',
                'cd.name',
            );
                
            if (in_array($sort, $sort_data)) {
                if ($sort == 'pd.name' || $sort == 'p.model' || $sort == 'm.name' || $sort == 'cd.name') {
                    $sql .= " ORDER BY LCASE(" . $sort . ")";
                } 
                else {
                    $sql .= " ORDER BY " . $sort;
                }
            } 
            else {
                $sql .= " ORDER BY p.sort_order";    
            }
            
            if ($order == 'DESC') {
                $sql .= " DESC";
            } 
            else {
                $sql .= " ASC";
            }

            if ($start < 0) {
                $start = 0;
            }            
            $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
            
            $query = $this->db->query($sql);
                          
            return $query->rows;
        }
        return 0;    
    }
    
    public function searchProductsByAttribute($keyword, $attribute_ids, $value_ids, $sort = 'pd.name', $order = 'ASC', $start = 0, $limit = 12) {
        if ($keyword) {                        
            //$sql = "SELECT p.*, pd.name AS name, p.image, ss.name AS stock, pov.price AS attribute_price, pcs.product_sort_order FROM " . DB_PREFIX . "product p" .
            $sql = "SELECT p.*, pd.name AS name, p.image, ss.name AS stock, pov.price AS attribute_price FROM " . DB_PREFIX . "product p" .
                " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)" . 
                " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_option po ON (p.product_id = po.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (po.product_option_id = pov.product_option_id)" .
                " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_to_category pc ON (p.product_id = pc.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "category_description cd ON (pc.category_id = cd.category_id)" .
                //" LEFT JOIN " . DB_PREFIX . "product_category_sort pcs ON (pcs.product_id = p.product_id AND pcs.category_id = pc.category_id) " .
                //" LEFT JOIN " . DB_PREFIX . "url_alias ua ON (CONCAT('product_id=', p.product_id) = ua.query) " .
                " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'" .
                " AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";        
           
            $sql .= " AND po.attribute_id IN (" . implode(',', $attribute_ids) . ") AND pov.attribute_value_id IN (" . implode(',', $value_ids) . ")";                
                        
            $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                "   OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                "   OR LCASE(pd.meta_keywords) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                //"   OR LCASE(ua.keyword) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                ")";                              
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() AND cd.language_id = '" . (int)$this->config->get('config_language_id'). "' GROUP BY p.product_id";
             
            $sort_data = array(
                'pd.name',
                //'p.sort_order',
                'pcs.product_sort_order',
                'special',
                'rating',
                'p.price',
                'p.model',
                'p.date_added',
                'm.name',
                'cd.name',
            );
                
            if (in_array($sort, $sort_data)) {
                if ($sort == 'pd.name' || $sort == 'p.model' || $sort == 'm.name' || $sort == 'cd.name') {
                    $sql .= " ORDER BY LCASE(" . $sort . ")";
                } 
                else {
                    $sql .= " ORDER BY " . $sort;
                }
            } 
            else {
                $sql .= " ORDER BY p.sort_order";    
            }
            
            if ($order == 'DESC') {
                $sql .= " DESC";
            } 
            else {
                $sql .= " ASC";
            }

            if ($start < 0) {
                $start = 0;
            }  
            $sql .= " LIMIT " . (int)$start . "," . (int)$limit;          
                  
            $query = $this->db->query($sql);
            
            return $query->rows;
        }
        return 0;    
    }
    
    public function getProductIdsByAttribute($keyword, $attribute_ids, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 12) {
        if ($keyword) {                        
            $sql = "SELECT DISTINCT p.product_id                  
                    FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd  
                    ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s  
                    ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "stock_status ss  
                    ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "product_option po  
                    ON (p.product_id = po.product_id) LEFT JOIN " . DB_PREFIX . "product_option_value pov
                    ON (po.product_option_id = pov.product_option_id)
                    WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
                    AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
                    AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'        
                    AND pov.attribute_value_id IN(" . implode(',', $attribute_ids) . ")";        
                        
            $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";             
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
            
            if ($start < 0) {
                $start = 0;
            }  
            if($limit > 0){    
                $sql .= " LIMIT " . (int)$start . "," . (int)$limit;    
            }
                  
            $query = $this->db->query($sql);
             
            $data = $query->rows;
            $results = array();
            foreach($data as $item){
                $results[] = $item['product_id'];
            }
            
            return $results;
        }
        return 0;    
    }
    
    public function getProductIds($keyword, $sort = 'p.sort_order', $order = 'ASC', $start = 0, $limit = 12) {
        if ($keyword) {                        
            $sql = "SELECT DISTINCT p.product_id                  
                    FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd  
                    ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s  
                    ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "stock_status ss  
                    ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "product_option po  
                    ON (p.product_id = po.product_id) LEFT JOIN " . DB_PREFIX . "product_option_value pov
                    ON (po.product_option_id = pov.product_option_id)  
                    WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
                    AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
                    AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'";        
                        
            $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";             
            $sql .= " AND p.status = '1' AND p.date_available <= NOW() GROUP BY p.product_id";
                         
            if ($start < 0) {
                $start = 0;
            }          
            if($limit > 0){  
                $sql .= " LIMIT " . (int)$start . "," . (int)$limit;
            }
            
            $query = $this->db->query($sql);            
            $data = $query->rows;
            $results = array();
            foreach($data as $item){
                $results[] = $item['product_id'];
            }
            
            return $results;
        }
        return 0;    
    }
    
    public function getTotalProductsByKeyword($keyword, $category_id = 0, $description = FALSE, $model = FALSE) {
        if ($keyword) {
            $sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p" . 
                " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)" .
                " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)" .
                //" LEFT JOIN " . DB_PREFIX . "url_alias ua ON (CONCAT('product_id=', p.product_id) = ua.query)" .
                " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
            
            if (!$description) {
                $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    " OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    //" OR LCASE(pd.meta_keywords) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    //" OR LCASE(ua.keyword) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
					"";
            } else {
                $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                " OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                //" OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'".
                //" OR LCASE(pd.meta_keywords) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" . 
                //" OR LCASE(ua.keyword) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" . 
				"";
            }

            if (!$model) {
                $sql .= ")";
            } else {
                $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
            }

            if ($category_id) {
                $data = array();
                
                $this->load->model('catalog/category');
                
                $string = rtrim($this->getPath($category_id), ',');
                
                foreach (explode(',', $string) as $category_id) {
                    $data[] = "category_id = '" . (int)$category_id . "'";
                }
                
                $sql .= " AND p.product_id IN (SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE " . implode(" OR ", $data) . ")";
            }
            
            $sql .= " AND p.status = '1' AND p.date_available <= NOW()";
            
            $query = $this->db->query($sql);
        
            if ($query->num_rows) {
                return $query->row['total'];
            } else {
                return 0;
            }
        }
        return 0;
    }
    
    public function getTotalProductsByKeywordAttribute($keyword, $attribute_ids, $value_ids, $description = FALSE) {
        if ($keyword) {
            $sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p " .  
                " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) " . 
                " LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) " .
                " LEFT JOIN " . DB_PREFIX . "product_option po ON (p.product_id = po.product_id) " .    
                " LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (po.product_option_id = pov.product_option_id) " .
                //" LEFT JOIN " . DB_PREFIX . "url_alias ua ON (CONCAT('product_id=', p.product_id) = ua.query) " .
                " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ";
            
            $sql .= " AND po.attribute_id IN (" . implode(',', $attribute_ids) . ") AND pov.attribute_value_id IN (" . implode(',', $value_ids) . ")";                
            
            if (!$description) {
                $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    "   OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    "   OR LCASE(pd.meta_keywords) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    //" OR LCASE(ua.keyword) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    ")";
            } 
            else {
                $sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    "   OR LCASE(p.sku) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    "   OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    "   OR LCASE(pd.meta_keywords) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    //" OR LCASE(ua.keyword) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'" .
                    ")";
            }
             
            $sql .= " AND p.status = '1' AND p.date_available <= NOW()";
             
            $query = $this->db->query($sql);
        
            if ($query->num_rows) {
                return $query->row['total'];
            } 
            else {
                return 0;
            }
        }
        return 0;
    }
    
    public function breakProductName($string, $length, $text_width = 220, $font_size = 12){      
        //return wordwrap($string, $length, "<br />");  
        
        $len = mb_strlen($string, 'UTF-8'); 
        if($len > $length){
            //$path = mb_substr($string, 0, $length * 2 + 20, 'UTF-8');            
            //$path = explode(' ', $path);
            $path = explode(' ', $string);
            
            $count = count($path) - 1; 
            $t = mb_strlen($path[0], 'UTF-8');
            $short_name = $path[0];     
            $pos = 0;       
            for($i = 1; $i <= $count; $i++){  
                $l = mb_strlen($path[$i], 'UTF-8');    //echo $path[$i] . "($l)*"; 
                $info = imagettfbbox($font_size, 0, DIR_DOWNLOAD . 'arial.ttf', $short_name . ' ' . $path[$i]);
                $pixel_width = $info[2] - $info[0];
                if($t + $l + 1 > $length || $pixel_width > $text_width){
                    $pos = $i;
                    break;                        
                }                  
                else{
                    $t += $l + 1; 
                    $short_name .= ' ' . $path[$i];    
                }
            }           
            //echo "?$len<br />";
            //$pos = mb_strlen($short_name, 'UTF-8');   
            //$str = mb_substr($string, $pos + 1, $length - 3, 'UTF-8');
            $str = trim(str_replace($short_name, '', $string));
            $str = mb_substr($str, 0, $length - 3, 'UTF-8');
            
            $sl = mb_strlen($str, 'UTF-8');
            if($sl > 0 && $sl ==  $length - 3){
                $short_name .= '<br />' . $str . '...';
            }
            else{
                $short_name .= '<br />' . $str;
            }
            
            return $short_name;
        }
        
        return $string;        
    }
    
    public function productShortName($string, $length){
        $short_name = (mb_strlen($string, 'UTF-8') > $length) ? (mb_substr($string, 0, $length, 'UTF-8') . ' ...') : $string;
        
        return $short_name;
    }
    
    public function getProductWeightClass($weight_class_id) {
        $query = $this->db->query("SELECT title, unit FROM " . DB_PREFIX . "weight_class_description WHERE weight_class_id = '" . (int)$weight_class_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
    
        return $query->row;
    }
    
    public function getProductLengthClass($length_class_id) {
        $query = $this->db->query("SELECT title, unit FROM " . DB_PREFIX . "length_class_description WHERE length_class_id = '" . (int)$length_class_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
    
        return $query->row;
    }
}
?>
